import pymysql
from todo import Todo

class TodoStorageWithMysql:
    def __init__(self):
        pass
    @staticmethod
    def get_connection():
        return pymysql.connect(host="192.168.79.129", user='root', password='Taoye424...', database="todo",
                                autocommit=True)

    def create(self,title:str):
        with self.get_connection()as conn:
            cursor=conn.cursor()
            cursor.execute("insert into todos(title) values(%s)",(title,))

            cursor.execute("select Last_insert_id()")
            (id,)=cursor.fetchone()
            return Todo(title,id=id)

    def init_db(self):

        with self.get_connection() as conn:

            create_sql='''
                 create table if not exists todos(
                    id int(10) not null primary key auto_increment,
                    title varchar(255) not null,
                    completed int(10) not null default 0
                );
                '''
            curson=conn.cursor()
            curson.execute(create_sql)

    def convert_row_to_object(self,row):
        return Todo(row[1],id=row[0],completed=True if row[2]==1 else False)

    def get_list(self):
        with self.get_connection()as conn:
            cursor=conn.cursor()
            cursor.execute("select * from todos")
            data=cursor.fetchall() #fetchall 返回元组类型的多条记录 fetchone() 一条记录
        list = []
        for row in data:
            list.append(self.convert_row_to_object(row))
        return list

    def get_by_id(self,id):
        with self.get_connection()as conn:
            cursor=conn.cursor()
            cursor.execute("select * from todos where todo_id = %s",(id,))
            data=cursor.fetchone()
            if data is not None:
                return self.convert_row_to_object(data)
            else:
                return None

    def mark_completed(self, id):
        with self.get_connection()as conn:
            cursor = conn.cursor()
            cursor.execute("update todos set completed=1 where id = %s", (id,))
            conn.commit()
            return self.get_by_id(id)


    def delete(self,id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("delete from todos where id=%s",(id,))
            conn.commit()
            return self.get_list()

    def over_completed(self):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("select * from todos where completed=1")
            over_list = []
            data=cursor.fetchall()
            for row in data:
                over_list.append(self.convert_row_to_object(row))
            return over_list

    def never_finished(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todos where completed=0")
            never_list = []
            data = cursor.fetchall()
            for row in data:
                never_list.append(self.convert_row_to_object(row))
            return never_list
a=TodoStorageWithMysql()
a.init_db()